#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
import re                                                                                                                                                                                                                                    
import xconfig as xcfg
host=xcfg.host
port=xcfg.db_port
user=xcfg.db_user
pwd=xcfg.db_pwd
db_name=xcfg.mdb_name

def insert(table_name,value_array):
    status="mysqldb ok"
    try:
        conn=MySQLdb.connect(host=host,port=port,user=user,passwd=pwd,db=db_name,charset="utf8",cursorclass = MySQLdb.cursors.SSCursor)
        cur=conn.cursor()
        
        rsorclass = MySQLdb.cursors.SSCursor
        flag=''
        for i in range(0,len(value_array)):
            if i==0:
                flag='%s'
            else:
                flag= flag+',%s'      
        sql= 'insert into '+table_name+' values('+flag+')'
        cur.execute(sql,value_array) 
        conn.commit()
        cur.close()
        conn.close()
    except MySQLdb.Error as e:  
        print(e)
        print ("Mysql Error %d: %s" % (e.args[0], e.args[1]))
        status="db wrong"
    return status  

def insertIgnore(table_name,value_array):
    status="db ok"
    try:
        conn=MySQLdb.connect(host=host,port=port,user=user,passwd=pwd,db=db_name,charset="utf8")
        cur=conn.cursor()
        flag=''
        for i in range(0,len(value_array)):
            if i==0:
                flag='%s'
            else:
                flag= flag+',%s'      
        sql= 'insert ignore into '+table_name+' values('+flag+')'
        cur.execute(sql,value_array) 
        conn.commit()
        cur.close()
        conn.close()
    except MySQLdb.Error as e:
            print( "Mysql Error %d: %s" % (e.args[0], e.args[1]))
            status="db wrong"
    return status  

def replace(table_name,value_array):
    status="db ok"
    try:
        conn=MySQLdb.connect(host=host,port=port,user=user,passwd=pwd,db=db_name,charset="utf8")
        cur=conn.cursor()
        flag=''
        for i in range(0,len(value_array)):
            if i==0:
                flag='%s'
            else:
                flag= flag+',%s'      
        sql= 'replace into '+table_name+' values('+flag+')'
        cur.execute(sql,value_array) 
        conn.commit()
        cur.close()
        conn.close()
    except MySQLdb.Error as e:
            print( "Mysql Error %d: %s" % (e.args[0], e.args[1]))
            status="db wrong"
    return status 



def replaceMany(table_name,data, columns=None):
    status="db ok"
    try:
        conn=MySQLdb.connect(host=host,port=port,user=user,passwd=pwd,db=db_name,charset="utf8")
        sql=None
        if columns is None:
            sql ="replace into %s values(%s)" %(table_name,
                                              ','.join(['%s']*len(data[0])) )
        else:
            columns=['`%s`'%(c) for c in columns]
            
            sql ="replace into %s(%s) values(%s)" %(table_name,
                                              ','.join(columns),
                                              ','.join(['%s']*len(columns)) )
        cur=conn.cursor()
        cur.executemany(sql, data )
        conn.commit()
        cur.close()
        conn.close()
        print('%s to mysql ok'%(table_name))
    #except Exception, e:
    #        print(e)
    except MySQLdb.Error as e:
            print('table "%s": %s,%s'%(table_name, e.args[0], e.args[1]))
            status="db wrong"
    return status 

def excute(sql):
    status=True
    try:
        conn=MySQLdb.connect(host=host,port=port,user=user,passwd=pwd,db=db_name,charset="utf8")
        cur=conn.cursor()
        cur.execute(sql)
        conn.commit()
        cur.close()
        conn.close()
    except MySQLdb.Error as e:
        print(sql)
        print( "Mysql Error %d: %s" % (e.args[0], e.args[1]))
        status=False
    return status


def select(sql):
    data=None
    if sql is not None and sql !='':
        try:
            conn=MySQLdb.connect(host=host,port=port,user=user,passwd=pwd,db=db_name,charset="utf8")
            cur=conn.cursor(cursorclass = MySQLdb.cursors.DictCursor) 
            cur.execute(sql)
            data = cur.fetchall()  
            cur.close()
            conn.close()
            #log.debug(("%s    :ok"%(sql)))
        except MySQLdb.Error as e:
            print( "Mysql Error %d: %s" % (e.args[0], e.args[1]))
            print(("%s     : select wrong"%(sql)))
            data= "Mysql Error %d: %s" % (e.args[0], e.args[1])
    return data


def select_tuple_dict(sql):
    data=None
    if sql is not None and sql!='':
        try:
            conn=MySQLdb.connect(host=host,port=port,user=user,passwd=pwd,db=db_name,charset="utf8")
            cur=conn.cursor(cursorclass = MySQLdb.cursors.DictCursor) 
            cur.execute(sql)
            data = cur.fetchall()  
            cur.close()
            conn.close()
            #log.debug(("%s    :ok"%(sql)))
        except MySQLdb.Error as e:
                print( "Mysql Error %d: %s" % (e.args[0], e.args[1]))
                print(("%s     : select wrong"%(sql)))
                data= "Mysql Error %d: %s" % (e.args[0], e.args[1])
    return data

def select_tuple_tuple(sql):
    data=None
    if sql is not None and sql!='':
        try:
            conn=MySQLdb.connect(host=host,port=port,user=user,passwd=pwd,db=db_name,charset="utf8")
            cur=conn.cursor(cursorclass = MySQLdb.cursors.SSCursor) 
            cur.execute(sql)
            data = cur.fetchall()  
            cur.close()
            conn.close()
            #log.debug(("%s    :ok"%(sql)))
        except MySQLdb.Error as e:
                print( "Mysql Error %d: %s" % (e.args[0], e.args[1]))
                print(("%s     : select wrong"%(sql)))
                data= "Mysql Error %d: %s" % (e.args[0], e.args[1])
    return data

def delete(table_name):  
    status=table_name+ " deleted ok"
    try:
            conn=MySQLdb.connect(host=host,port=port,user=user,passwd=pwd,db=db_name,charset="utf8")
            cur=conn.cursor(cursorclass = MySQLdb.cursors.SSCursor)         
            sql= 'delete from '+table_name;
           
            cur.execute(sql) 
            conn.commit()
            cur.close()
            conn.close()
    except MySQLdb.Error as e:
            print( "Mysql Error %d: %s" % (e.args[0], e.args[1]))
            status="db wrong"
    return status  

def deletAll():  
    status="db ok"
    try:
            conn=MySQLdb.connect(host=host,port=port,user=user,passwd=pwd,db=db_name,charset="utf8")
            cur=conn.cursor(cursorclass = MySQLdb.cursors.SSCursor)         
            sql= 'delete from group_info;'
            sql+= 'delete from owner_interested;'
            sql+= 'delete from stocks_changing;'
            sql+= 'delete from stock;'
            sql+= 'delete from realtime_stock;'
            sql+= 'delete from score_info;'
            sql+= 'delete from realtime_stock;'
            sql+= 'delete from hist_exchange;'
            cur.execute(sql) 
            #conn.commit()
            cur.close()
            conn.close()
    except MySQLdb.Error as e:
                print( "Mysql Error %d: %s" % (e.args[0], e.args[1]))
                status="db wrong"
    return status  

def selectAll(tablename):
    data=None
    try:
        conn=MySQLdb.connect(host=host,port=port,user=user,passwd=pwd,db=db_name,charset="utf8")
        cur=conn.cursor(cursorclass = MySQLdb.cursors.DictCursor) 
        cur.execute("select * from "+tablename)
        data = cur.fetchall()  
        cur.close()
        conn.close()
        #log.debug("select db ok")
    except MySQLdb.Error as e:
            print( "Mysql Error %d: %s" % (e.args[0], e.args[1]))
            print("select db wrong")
    return data


def table_exists(table_name):        #这个函数用来判断表是否存在
    
    try:
        conn=MySQLdb.connect(host=host,port=port,user=user,passwd=pwd,db=db_name,charset="utf8")
        cur = conn.cursor()
        cur.execute("show tables;")
        tables = [cur.fetchall()]
        table_list = re.findall('(\'.*?\')',str(tables))
        table_list = [re.sub("'",'',each) for each in table_list]
        if table_name in table_list:
            return True        #存在返回1
        else:
            return False        #不存在返回0
        cur.close()
        conn.close()
    except MySQLdb.Error as e:
            print( "Mysql Error %d: %s" % (e.args[0], e.args[1]))
            print("exist table wrong")

def replaceManyToMysql(tableName,listOfDict, package_id=None,isAsText=[]):
    valList=[]
    for i in range(len(listOfDict)):
        if i==0:
            if table_exists(tableName):
                if package_id is not None:
                    excute('delete from %s where package_id="%s"'%(tableName,package_id))
            else:
                create_str='CREATE TABLE `%s` ('%(tableName)
                for c in listOfDict[0].keys():
                    r=''
                    if c=='_id':
                        r='`%s` VARCHAR(100) NULL,'%(c)
                    else:
                        if c in isAsText:
                            r='`%s` TEXT NULL,'%(c)
                        else:
                            r='`%s` VARCHAR(45) NULL,'%(c)
                    create_str=create_str+r
                
                create_str=create_str+ 'PRIMARY KEY (`_id`)) ENGINE=InnoDB DEFAULT CHARACTER SET utf8';
                excute(create_str)
        rowList=[]
        for k in listOfDict[i].keys():
            d=listOfDict[i][k]
            rowList.append(d)
        
        valList.append(rowList)
        
    replaceMany(tableName, valList)
    

def dropTable(tableName):
    if table_exists(tableName):
        drop_sql='drop table %s'%(tableName)
        excute(drop_sql)

def creatTable(tableName,columns,drop_exist=False,addition_sql=None,isAsText=[]):
    if table_exists(tableName):
        if drop_exist:
            dropTable(tableName)
            creatTableExcu(tableName,columns)
        else:
            if addition_sql is not None and addition_sql is not '':
                excute(addition_sql)
    else:
        creatTableExcu(tableName,columns,isAsText=isAsText)

def creatTableExcu(tableName,columns,isAsText=[]):
    create_str='CREATE TABLE `%s` ('%(tableName)
    for c in columns:
        r=''
        if c=='_id':
            r='`%s` VARCHAR(100) NULL,'%(c)
        else:
            if c in isAsText:
                r='`%s` TEXT NULL,'%(c)
            else:
                r='`%s` VARCHAR(45) NULL,'%(c)
        create_str=create_str+r
    
    create_str=create_str+ 'PRIMARY KEY (`_id`)) ENGINE=InnoDB DEFAULT CHARACTER SET utf8';
    excute(create_str)
    #return True   

def creatTableReplaceMany(tableName, df,drop_exist=False,addition_sql=None,isAsText=[]):
    creatTable(tableName,df.columns,drop_exist=drop_exist,addition_sql=addition_sql,isAsText=isAsText)
    replaceMany(tableName,df.values.astype(str).tolist(), columns=df.columns)
   
    
if __name__ == '__main__':
    #print(select_tuple_tuple('select * from index_mstock'))
    sql='CREATE TABLE `backtest` (\
      `settlePriceChange` varchar(45) DEFAULT NULL,\
      `optID` varchar(45) DEFAULT NULL,\
      `nextClearDate` varchar(45) DEFAULT NULL,\
      `nextExpireDate` varchar(45) DEFAULT NULL,\
      `package_id` varchar(45) DEFAULT NULL,\
      `settlePrice` varchar(45) DEFAULT NULL,\
      `actionID` varchar(45) DEFAULT NULL,\
      `tradeDate` varchar(45) DEFAULT NULL,\
      `xtime` varchar(45) DEFAULT NULL,\
      `dayToExpire` varchar(45) DEFAULT NULL,\
      `w` varchar(45) DEFAULT NULL,\
      `_id` varchar(45) NOT NULL\
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;'
    
    if not table_exists('future_store'):
        excute(sql)
        print(1)
    else:
        print(0)
    


